package com.fastfood.model.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.fastfood.model.dto.Order;
import com.fastfood.util.OrderStatus;

public class OrderDAO {

	public boolean addOrder(Connection conn, Order order) {
		String query = "insert into orders (date, status, total) values (?, ?, ?)";
		boolean result = false;
		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setDate(1, order.getDate());
			preparedStatement.setInt(2, order.getStatus());
			preparedStatement.setFloat(3, order.getTotal());
			preparedStatement.executeUpdate();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean deleteOrder(Connection conn, Order order) {
		String query = "delete from orders where id = ?";
		boolean result = false;
		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setInt(1, order.getId());
			preparedStatement.executeUpdate();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public boolean updateOrder(Connection conn, Order order) {
		String query = "update orders set date = ?, status = ?, total = ? where id = ?";
		boolean result = false;
		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setDate(1, order.getDate());
			preparedStatement.setInt(2, order.getStatus());
			preparedStatement.setFloat(3, order.getTotal());;
			preparedStatement.setInt(4, order.getId());
			preparedStatement.executeUpdate();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	public List<Report> getReportsByYear(Connection conn, int year) {
//		String query = "select month(date) as month, count(*) as amount_order, sum(total) as grand_total from orders"
//						+ " where year(date) = ? group by month(date)";
		List<Report> reports = null;
//		try {
//			PreparedStatement preparedStatement = conn.prepareStatement(query);
//			preparedStatement.setInt(1, year);
//			//preparedStatement.setInt(2, OrderStatus.FINISHED);
//			ResultSet result = preparedStatement.executeQuery();
//			reports = new ArrayList<Report>();
//			while (result.next()) {
//				Report temp = new Report();
//				temp.setMonth(result.getInt("month"));
//				temp.setYear(year);
//				temp.setAmountOrder(result.getInt("amount_order"));
//				temp.setGrandTotal(result.getFloat("grand_total"));
//				reports.add(temp);
//			}
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
		return reports;
	}
	
	public List<Report> getReportsByDate(Connection conn, Date startDate, Date endDate) {
		String query = "select date, count(*) as amount_order, sum(total) as grand_total from orders"
						+ " where date >= ? and date <= ? and status = ? group by date";
		List<Report> reports = null;
		try {
			PreparedStatement preparedStatement = conn.prepareStatement(query);
			preparedStatement.setDate(1, startDate);
			preparedStatement.setDate(2, endDate);
			preparedStatement.setInt(3, OrderStatus.FINISHED);
			ResultSet result = preparedStatement.executeQuery();
			reports = new ArrayList<Report>();
			while (result.next()) {
				Report temp = new Report();
				temp.setDate(result.getDate("date"));
				temp.setAmountOrder(result.getInt("amount_order"));
				temp.setGrandTotal(result.getFloat("grand_total"));
				reports.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return reports;
	}
}
